<?php
   function print_excel($sql)
   {
   

    	$html         = '';  
	     require_once('Classes/PHPExcel.php');  
	  
	    // echo date('H:i:s') . " Create new PHPExcel object\n";  
	    $objPHPExcel = new PHPExcel();  
	  
	    // echo date('H:i:s') . " Set properties\n";  
	    $objPHPExcel->getProperties()->setCreator("wdz")->setLastModifiedBy("wdz")->setTitle("我的订单")->setSubject("我的订单")->setDescription(date('Y/m/d H:i:s') . "导出的订单")->setKeywords("我的订单")->setCategory("Test result file");  
	  
	    /*设置标题属性*/  
	    //字体大小  
	    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setSize(16);  
	    //加粗  
	    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getFont()->setBold(true);  
	    //水平居中  
	    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
	    //垂直居中  
	    $objPHPExcel->getActiveSheet()->getStyle('A1:J1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);  
	    //表格宽度  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(50);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(50);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);  
	    $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(100); 
	    $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(100);   
	  
	    // echo date('H:i:s') . " Add some data\n";  
	    $objPHPExcel->setActiveSheetIndex(0);  
	    $objPHPExcel->getActiveSheet()->setCellValue('A1', '序号'); 
	    $objPHPExcel->getActiveSheet()->setCellValue('B1', '姓名');  
	    $objPHPExcel->getActiveSheet()->setCellValue('C1', '电话');  
	    $objPHPExcel->getActiveSheet()->setCellValue('D1', '报名的旅游项目');  
	    $objPHPExcel->getActiveSheet()->setCellValue('E1', '班期');  
	    $objPHPExcel->getActiveSheet()->setCellValue('F1', '报名时间');  
	    $objPHPExcel->getActiveSheet()->setCellValue('G1', '所需支付金额');  
	    $objPHPExcel->getActiveSheet()->setCellValue('H1', '支付状态');  
	    $objPHPExcel->getActiveSheet()->setCellValue('I1', '详细');  
	    $objPHPExcel->getActiveSheet()->setCellValue('J1', '备注');  
	      
	    $hang = 2;  
	    //////////////////////////////////////////////////////////////
	  
	    $order_query = mysql_query($sql);
	    $num=mysql_num_rows($order_query); 
	    if($num<1)
	    { $_SESSION['excel_url']='';return '当前无已订单，导出失败。';}

	  while($order_value = mysql_fetch_array($order_query)) {
			$id_self=$order_value['id_self'];
			$openid=$order_value['openid'];
			$textarea=$order_value['textarea']?$order_value['textarea']:'备注为空';

			       // $no_number++;
			//查询用户姓名 电话
			$sql1="select * from `ecs_users` where openid='".$order_value['openid']."'";
			$query1 = mysql_query($sql1);
			while($row1 = mysql_fetch_array($query1)){
				$user_name=$row1['user_name'];
				$mobile_phone=$row1['mobile_phone'];
			}
			//查询报名的旅游线路名
			$sql1="select * from `lvyou_difang_info` where id='".$order_value['id_difang']."'";
			$query1 = mysql_query($sql1);
			while($row1 = mysql_fetch_array($query1)){
				$biaoti=$row1['biaoti'];
			}
			//获取支付状态 0未支付 1-已支付 2-已取消
			if($order_value['is_zhifu']==0){
				$is_zhifu="未支付";
			}
			if($order_value['is_zhifu']==1){
				$is_zhifu="已支付";
			}
			if($order_value['is_zhifu']==2){
				$is_zhifu="已取消";
			}
	     
	    $objPHPExcel->getActiveSheet()->setCellValue('A'.$hang, $order_value['id_self']);  
	    $objPHPExcel->getActiveSheet()->getStyle('A'.$hang)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);  

	    $objPHPExcel->getActiveSheet()->setCellValue('B'.$hang, $user_name);
	    $objPHPExcel->getActiveSheet()->setCellValue('C'.$hang, $mobile_phone); 

	    $objPHPExcel->getActiveSheet()->setCellValue('D'.$hang, cut_biaoti($biaoti,16));  
	    $objPHPExcel->getActiveSheet()->setCellValue('E'.$hang, $order_value['banqi']);  


	    $objPHPExcel->getActiveSheet()->setCellValue('F'.$hang, $order_value['issuetime']);
	    $pay_points=$order_value['pay_points'];
		$pay_mo= $order_value['zhifu_monkey']."元";
		if($pay_points>0)
	    $pay_mo="(使用驴币".$pay_points.")";
	    $objPHPExcel->getActiveSheet()->setCellValue('G'.$hang, $pay_mo);  
	    $objPHPExcel->getActiveSheet()->setCellValue('H'.$hang, $is_zhifu);  


		$row_list='';
		/******************购买人*********************/


		$sql="SELECT * FROM `ecs_users` where openid='$openid' and openid!='' ";
		$query = mysql_query($sql);


		while($row = mysql_fetch_array($query)){
			if($row['sex']==0){
				$sex="保密";
			}
			if($row['sex']==1){
				$sex="男";
			}
			if($row['sex']==2){
				$sex="女";
			}
			$row_list.=$row['real_name']."(购买人)".'-'.$sex.'-'.$row['mobile_phone'].'-'.$row['shenfen_ID'].",";
		}



		$sql="SELECT * FROM `fu_user` where id='$id_self' ";

		$query = mysql_query($sql);
		while($row = mysql_fetch_array($query)){
			if($row['choose']==3){
				$sex="儿童";
			}
			if($row['choose']==1){
				$sex="男";
			}
			if($row['choose']==2){
				$sex="女";
			}
			$row_list.=$row['name']."(随同)".'-'.$sex.'-'.$row['phone'].'-'.$row['idcode'].",";

		}

	    $objPHPExcel->getActiveSheet()->setCellValue('I'.$hang, $row_list);  
	    $objPHPExcel->getActiveSheet()->setCellValue('J'.$hang, $textarea);  
	  

	      $hang++;

	  }
	  /******************购买人*********************/


	    $objPHPExcel->getActiveSheet()->setTitle(("我的订单"));  
	    $objPHPExcel->setActiveSheetIndex(0);  
	  
	   require_once('Classes/PHPExcel/IOFactory.php'); 
	   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
	   $file_name="order".date("Y-m-d",time()).time().".xls";
	   $objWriter->save('file_temp/'.$file_name);  
	  
      
        
	    $url="http://haining.365jinbi.com/lvxingtianxia/Admin/file_temp/". $file_name;
	    $_SESSION['excel_url']=$url;
		return '订单导出失败。';
    }
?>